import openpyxl
import pymysql
import re


def selectEcu(vehicle_model_id, ecu_name):
    # SQL 查询语句
    sql = "SELECT * FROM tab_ecu WHERE vehicle_model_id='" + vehicle_model_id + "' AND ecu_name LIKE '%%%%%s%%%%'"
    sql = sql % (ecu_name)
    # 执行SQL语句
    cursor.execute(sql, ())
    # 获取一条记录列表
    return cursor.fetchone()


def addDtc(item):
    into = "insert into `tab_dtc` (`dtc_id`, `ecu_id`, `dtc_code`, `message_EN`, `message_CN`) values(%s,%s,%s,%s,%s)"
    values = (item['dtc_id'], item['ecu_id'], item['dtc_code'], item['message_EN'], item['message_CN'])
    cursor.execute(into, values)
    db.commit()


# 建立临时输出文件
f = open("out.txt", "w")  # 打开文件以便写入
# 建立mysql链接
db = pymysql.connect(host='gz-cdb-o43ukrr9.sql.tencentcdb.com',
                       port=60652,
                       user='dskj',
                       passwd='Dskj!2019',
                       db='bss-gq',
                       charset='utf8')
cursor = db.cursor()  # 使用cursor()方法获取操作游标
# 打开excel文件,获取工作簿对象
wb = openpyxl.load_workbook("C:\\Users\\wangle\\Desktop\\x9e\\新增车型\\A26-故障码库.xlsx")

# 获取workbook中所有的表格
sheets = wb.sheetnames
# 车系
vehicle_ser = "7"
# 车型
vehicle_model_id = "20"
number = 1
item = {}

print(sheets, file=f)
blankMap = {}
ecuMap = {}

for sheet in sheets:
    if sheet == "A26故障码库":
        ws = wb[sheet]
        for i in range(4, ws.max_row + 1):
            ecuCode = ws.cell(row=i, column=2).value
            if ecuCode in ecuMap:
                continue
            if ecuCode not in blankMap:
                # 查询是否有Ecu
                ecu = selectEcu(vehicle_model_id, ecuCode)
                # 查询不到放弃存储
                if ecu is None:
                    print("未匹配的" + sheet, file=f)
                    # 将没有值的ecu缓存
                    blankMap[ecuCode] = ecuCode
                else:
                    ecuMap[ecuCode] = ecu
        dtcList = []
        # 查询是否是重复
        for i in range(4, ws.max_row + 1):
            ecuCode = ws.cell(row=i, column=2).value
            if ecuCode not in ecuMap:
                continue
            item['dtc_id'] = vehicle_ser + vehicle_model_id + "%04d" % number
            item['ecu_id'] = ecuMap[ecuCode][0]
            dtcCode = ws.cell(row=i, column=4).value
            # 故障码为空放弃
            if dtcCode is None:
                continue   
            item['dtc_code'] = dtcCode
            en = ws.cell(row=i, column=6).value
            cn = ws.cell(row=i, column=7).value
            if not en is None:
                item['message_EN'] = en.replace(" ", "")
            else:
                item['message_EN'] = ""
            if not cn is None:
                item['message_CN'] = cn.replace(" ", "")
            else:
                item['message_CN'] = ""
            # item['message_CN'] = ws.cell(row=i, column=7).value.replace(" ", "")
            print(item)
            addDtc(item)
            number = number + 1
        # if sheet in map:
        #     dtcList = map[sheet]
        #     for i in range(3, ws.max_row + 1):
        #         item['dtc_id'] = vehicle_ser + vehicle_model_id + "%04d" % number
        #         item['ecu_id'] = ecu[0]
        #         dtcCode = ws.cell(row=i, column=2).value
        #         # 故障码为空放弃
        #         if dtcCode is None:
        #             continue
        #         dtcCode = str(dtcCode)
        #         dtcCode = dtcCode.strip().replace("\n", "")
        #         if dtcCode.find("(") != -1:
        #             dtcCode = dtcCode[0:dtcCode.find("(")]
        #         if dtcCode.find("（") != -1:
        #             dtcCode = dtcCode[0:dtcCode.find("（")]
        #         if dtcCode.find("删除") != -1:
        #             continue
        #         # 已存在放弃
        #         if dtcCode in dtcList:
        #             continue
        #         item['dtc_code'] = dtcCode
        #         item['message_EN'] = ws.cell(row=i, column=3).value
        #         item['message_CN'] = ws.cell(row=i, column=4).value
        #         print(item, file=f)
        #         number = number + 1
        #         dtcList.append(dtcCode)
        #         # addDtc(item)
        #     map[sheet] = dtcList
